Poverty Rate is greater than or equal to 20% or a household with income less than the federal poverty level of USD 17,050 for a family of four in 2000
At least 500 people or 33% of the population is located more than 1 mile (Urban) and 10 miles (Rural) from the nearest supermarket or grocery store
### Importing GIS Libraries
import geopandas as gpd
import plotly.graph_objects as go
### Importing data preprocessing & visualization libraries
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import seaborn as sns
### Importing warning library
import warnings
warnings.filterwarnings("ignore")
### Storing New York Map coordinates of 2019 as a pandas dataframe
ny_map = gpd.read_file(r'C:\Users\tuhin\OneDrive\Desktop\Omdena Brooklyn\cb_2019_36_tract_500k.shp', encoding='utf-8')
### Checking how the dataframe looks like
ny_map.head(2)
| STATEFP | COUNTYFP | TRACTCE | AFFGEOID | GEOID | NAME | LSAD | ALAND | AWATER | geometry | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 36 | 071 | 000502 | 1400000US36071000502 | 36071000502 | 5.02 | CT | 967431 | 969216 | POLYGON ((-74.02226 41.49281, -74.02180 41.496... |
| 1 | 36 | 103 | 135208 | 1400000US36103135208 | 36103135208 | 1352.08 | CT | 2287077 | 0 | POLYGON ((-73.28263 40.83063, -73.28157 40.832... |
### Plotting the map of New York state
fig, ax = plt.subplots(figsize=(20,20))
ny_map.plot(ax=ax)
plt.show()
### Checking if all New York Counties are present in the dataframe
print("The total number of counties present in New York is", ny_map['COUNTYFP'].nunique())
print("Showing all county codes")
ny_map["COUNTYFP"].unique()
The total number of counties present in New York is 62 Showing all county codes
array(['071', '103', '047', '055', '005', '029', '061', '119', '033',
'001', '085', '069', '067', '081', '007', '107', '051', '059',
'073', '015', '063', '053', '065', '083', '027', '117', '009',
'057', '037', '093', '021', '087', '121', '111', '105', '043',
'019', '109', '023', '011', '113', '091', '035', '013', '099',
'075', '039', '089', '045', '101', '095', '003', '025', '049',
'097', '115', '017', '031', '077', '079', '123', '041'],
dtype=object)
## Select "Kings County" which is code - 047 (Brooklyn Borough)
kings_map = ny_map[ny_map["COUNTYFP"]=="047"]
## Checking the column headers of kings dataframe
kings_map.head(5)
| STATEFP | COUNTYFP | TRACTCE | AFFGEOID | GEOID | NAME | LSAD | ALAND | AWATER | geometry | |
|---|---|---|---|---|---|---|---|---|---|---|
| 2 | 36 | 047 | 057800 | 1400000US36047057800 | 36047057800 | 578 | CT | 172233 | 0 | POLYGON ((-73.95398 40.60140, -73.95304 40.601... |
| 3 | 36 | 047 | 058900 | 1400000US36047058900 | 36047058900 | 589 | CT | 424025 | 38353 | POLYGON ((-73.94605 40.72926, -73.94419 40.729... |
| 18 | 36 | 047 | 037000 | 1400000US36047037000 | 36047037000 | 370 | CT | 336242 | 0 | POLYGON ((-73.96615 40.58667, -73.96564 40.588... |
| 19 | 36 | 047 | 019900 | 1400000US36047019900 | 36047019900 | 199 | CT | 188914 | 0 | POLYGON ((-73.96949 40.68629, -73.96749 40.686... |
| 26 | 36 | 047 | 040000 | 1400000US36047040000 | 36047040000 | 400 | CT | 192000 | 0 | POLYGON ((-73.98078 40.59845, -73.97986 40.598... |
### Reading the Food Access Research Atlas of 2019
kings_foodaccess = pd.read_csv('kings_data.csv')
kings_foodaccess.head(2)
| CensusTract | Track Code | State | County | Urban | Pop2010 | OHU2010 | GroupQuartersFlag | NUMGQTRS | PCTGQTRS | ... | TractSeniors | TractWhite | TractBlack | TractAsian | TractNHOPI | TractAIAN | TractOMultir | TractHispanic | TractHUNV | TractSNAP | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 36047000100 | 100 | New York | Kings County | 1 | 4338 | 2227 | 0 | 424 | 9.77 | ... | 843 | 3396 | 427 | 330 | 0 | 15 | 170 | 347 | 1417 | 126 |
| 1 | 36047000200 | 200 | New York | Kings County | 1 | 1470 | 360 | 0 | 39 | 2.65 | ... | 70 | 530 | 103 | 49 | 0 | 16 | 772 | 1277 | 265 | 138 |
2 rows × 148 columns
### Imputing leading zeroes which usually gets removed when reading a csv file
kings_foodaccess['Track Code'] = kings_foodaccess['Track Code'].astype(str)
kings_foodaccess['Track Code'] = kings_foodaccess['Track Code'].str.zfill(6)
kings_foodaccess.head(2)
| CensusTract | Track Code | State | County | Urban | Pop2010 | OHU2010 | GroupQuartersFlag | NUMGQTRS | PCTGQTRS | ... | TractSeniors | TractWhite | TractBlack | TractAsian | TractNHOPI | TractAIAN | TractOMultir | TractHispanic | TractHUNV | TractSNAP | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 36047000100 | 000100 | New York | Kings County | 1 | 4338 | 2227 | 0 | 424 | 9.77 | ... | 843 | 3396 | 427 | 330 | 0 | 15 | 170 | 347 | 1417 | 126 |
| 1 | 36047000200 | 000200 | New York | Kings County | 1 | 1470 | 360 | 0 | 39 | 2.65 | ... | 70 | 530 | 103 | 49 | 0 | 16 | 772 | 1277 | 265 | 138 |
2 rows × 148 columns
### TRACTCE will be the primary key to link all the database.
## Renaming Tract Code as "TRACTCE"
kings_foodaccess = kings_foodaccess.rename(columns={'Track Code': 'TRACTCE'})
kings_foodaccess.head(2)
| CensusTract | TRACTCE | State | County | Urban | Pop2010 | OHU2010 | GroupQuartersFlag | NUMGQTRS | PCTGQTRS | ... | TractSeniors | TractWhite | TractBlack | TractAsian | TractNHOPI | TractAIAN | TractOMultir | TractHispanic | TractHUNV | TractSNAP | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 36047000100 | 000100 | New York | Kings County | 1 | 4338 | 2227 | 0 | 424 | 9.77 | ... | 843 | 3396 | 427 | 330 | 0 | 15 | 170 | 347 | 1417 | 126 |
| 1 | 36047000200 | 000200 | New York | Kings County | 1 | 1470 | 360 | 0 | 39 | 2.65 | ... | 70 | 530 | 103 | 49 | 0 | 16 | 772 | 1277 | 265 | 138 |
2 rows × 148 columns
### We need to map TRACTCE in kings database and TRACTCE in Food Atlas 2019 database to create a single database
first_compiled = kings_map.merge(kings_foodaccess, on='TRACTCE')
### Checking the total number of census tracks present in Kings County (Brooklyn Borough)
first_compiled['TRACTCE'].nunique()
754
### Visualizing the Brooklyn Borough with Poverty Rate as one of the key metric
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=first_compiled.geometry.__geo_interface__,
locations=first_compiled.index,
z=first_compiled['PovertyRate'],
colorscale='OrRd',
zmin=first_compiled['PovertyRate'].min(),
zmax=first_compiled['PovertyRate'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover option Over Text
hover_text = []
for idx, row in first_compiled.iterrows():
hover_text.append(f"Census Tract Area: {row['TRACTCE']}<br>PovertyRate: {row['PovertyRate']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": first_compiled.geometry.centroid.y.mean(),
"lon": first_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='Poverty Rate')
fig.show()
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=first_compiled.geometry.__geo_interface__,
locations=first_compiled.index,
z=first_compiled['LILATracts_halfAnd10'],
colorscale='OrRd',
zmin=first_compiled['LILATracts_halfAnd10'].min(),
zmax=first_compiled['LILATracts_halfAnd10'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in first_compiled.iterrows():
hover_text.append(f"Census Tract Area: {row['TRACTCE']}<br>Low Income & Low Access - 1/2 mile: {row['PovertyRate']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": first_compiled.geometry.centroid.y.mean(), "lon": first_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='LILATracts_halfAnd10')
fig.show()
This database contains the following
2017 Grocery data was used as a proxy to 2019 Food Access Research Atlas data
grocery_data = pd.read_csv('Grocery store database.csv')
grocery_data.head(2)
| tract_fips10 | year | population | aland10 | count_445110 | count_sales_445110 | count_emp_445110 | popden_445110 | popden_sales_445110 | popden_emp_445110 | ... | aden_emp_4452 | count_452311 | count_sales_452311 | count_emp_452311 | popden_452311 | popden_sales_452311 | popden_emp_452311 | aden_452311 | aden_sales_452311 | aden_emp_452311 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1001020100 | 2003 | 1858.883 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 1 | 1001020100 | 2004 | 1852.185 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
2 rows × 31 columns
grocery_data['tract_fips10'] = grocery_data['tract_fips10'].astype(str)
grocery_data['tract_fips10'] = grocery_data['tract_fips10'].str.zfill(11)
grocery_data.head(2)
| tract_fips10 | year | population | aland10 | count_445110 | count_sales_445110 | count_emp_445110 | popden_445110 | popden_sales_445110 | popden_emp_445110 | ... | aden_emp_4452 | count_452311 | count_sales_452311 | count_emp_452311 | popden_452311 | popden_sales_452311 | popden_emp_452311 | aden_452311 | aden_sales_452311 | aden_emp_452311 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 01001020100 | 2003 | 1858.883 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 1 | 01001020100 | 2004 | 1852.185 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
2 rows × 31 columns
# Extracting portions
grocery_data['State_Code'] = grocery_data['tract_fips10'].str[:2]
grocery_data['County_Code'] = grocery_data['tract_fips10'].str[2:5]
grocery_data['Census_Tract_Code'] = grocery_data['tract_fips10'].str[-6:]
grocery_data.head(2)
| tract_fips10 | year | population | aland10 | count_445110 | count_sales_445110 | count_emp_445110 | popden_445110 | popden_sales_445110 | popden_emp_445110 | ... | count_emp_452311 | popden_452311 | popden_sales_452311 | popden_emp_452311 | aden_452311 | aden_sales_452311 | aden_emp_452311 | State_Code | County_Code | Census_Tract_Code | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 01001020100 | 2003 | 1858.883 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 01 | 001 | 020100 |
| 1 | 01001020100 | 2004 | 1852.185 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 01 | 001 | 020100 |
2 rows × 34 columns
### Selecting New York State (State Code = 36)
grocery_ny = grocery_data[grocery_data["State_Code"]=="36"]
### Selecting Kings County (county code = 047)
grocery_kings = grocery_ny[grocery_ny['County_Code'] == '047']
grocery_kings
| tract_fips10 | year | population | aland10 | count_445110 | count_sales_445110 | count_emp_445110 | popden_445110 | popden_sales_445110 | popden_emp_445110 | ... | count_emp_452311 | popden_452311 | popden_sales_452311 | popden_emp_452311 | aden_452311 | aden_sales_452311 | aden_emp_452311 | State_Code | County_Code | Census_Tract_Code | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 620074 | 36047000100 | 2003 | 5254.272 | 0.08627 | 4 | 4 | 4 | 0.761285 | 0.761285 | 0.761285 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 | 000100 |
| 620075 | 36047000100 | 2004 | 5164.090 | 0.08627 | 4 | 4 | 4 | 0.774580 | 0.774580 | 0.774580 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 | 000100 |
| 620076 | 36047000100 | 2005 | 5073.908 | 0.08627 | 3 | 3 | 3 | 0.591260 | 0.591260 | 0.591260 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 | 000100 |
| 620077 | 36047000100 | 2006 | 4983.727 | 0.08627 | 3 | 2 | 3 | 0.601959 | 0.401306 | 0.601959 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 | 000100 |
| 620078 | 36047000100 | 2007 | 4893.545 | 0.08627 | 4 | 3 | 4 | 0.817403 | 0.613053 | 0.817403 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 | 000100 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 630723 | 36047990100 | 2012 | 0.000 | 0.00000 | 0 | 0 | 0 | NaN | NaN | NaN | ... | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 36 | 047 | 990100 |
| 630724 | 36047990100 | 2013 | 0.000 | 0.00000 | 0 | 0 | 0 | NaN | NaN | NaN | ... | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 36 | 047 | 990100 |
| 630725 | 36047990100 | 2014 | 0.000 | 0.00000 | 0 | 0 | 0 | NaN | NaN | NaN | ... | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 36 | 047 | 990100 |
| 630726 | 36047990100 | 2015 | 0.000 | 0.00000 | 0 | 0 | 0 | NaN | NaN | NaN | ... | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 36 | 047 | 990100 |
| 630727 | 36047990100 | 2017 | 0.000 | 0.00000 | 0 | 0 | 0 | NaN | NaN | NaN | ... | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 36 | 047 | 990100 |
10654 rows × 34 columns
### TRACTCE will be the primary key to link all the database.
## Renaming Census Tract Code as "TRACTCE"
grocery_kings = grocery_kings.rename(columns={'Census_Tract_Code': 'TRACTCE'})
### Selecting only 2017 data
grocery_kings_17 = grocery_kings[grocery_kings['year'] == 2017]
grocery_kings_17['TRACTCE'].nunique()
761
### We need to map TRACTCE in first_compiled and TRACTCE in grocery database to create a single database
second_compiled = first_compiled.merge(grocery_kings_17, on='TRACTCE')
second_compiled.head(2)
| STATEFP | COUNTYFP | TRACTCE | AFFGEOID | GEOID | NAME | LSAD | ALAND | AWATER | geometry | ... | count_sales_452311 | count_emp_452311 | popden_452311 | popden_sales_452311 | popden_emp_452311 | aden_452311 | aden_sales_452311 | aden_emp_452311 | State_Code | County_Code | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 36 | 047 | 057800 | 1400000US36047057800 | 36047057800 | 578 | CT | 172233 | 0 | POLYGON ((-73.95398 40.60140, -73.95304 40.601... | ... | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 |
| 1 | 36 | 047 | 058900 | 1400000US36047058900 | 36047058900 | 589 | CT | 424025 | 38353 | POLYGON ((-73.94605 40.72926, -73.94419 40.729... | ... | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 |
2 rows × 190 columns
second_compiled.info()
<class 'geopandas.geodataframe.GeoDataFrame'> Int64Index: 754 entries, 0 to 753 Columns: 190 entries, STATEFP to County_Code dtypes: float64(133), geometry(1), int64(44), object(12) memory usage: 1.1+ MB
socio = pd.read_csv('socio_eco_data.csv')
socio.head(2)
| TRACT_FIPS20 | ALAND20 | TOTPOP20 | POPDEN16_20 | PHISPANIC16_20 | PNHWHITE16_20 | PNHBLACK16_20 | PFBORN16_20 | PLIMENG16_20 | PED1_16_20 | ... | POWNOC16_20 | AFFLUENCE16_20 | DISADVANTAGE16_20 | HISPAN_FORBORN_LIMENG16_20 | MEDFAMINC16_20 | MEDFAMINC_NHWHITE16_20 | MEDFAMINC_BLACK16_20 | MEDFAMINC_HISPANIC16_20 | RATIO_MEDFAMINC_NHWTOB16_20 | RATIO_MEDFAMINC_NHWTOHISP16_20 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1001020100 | 3.793571 | 1941 | 511.6551209 | 0.017001545 | 0.774858296 | 0.121071614 | 0.002060793 | 0 | 0.142857149 | ... | 0.731601715 | 0.256776839 | 0.171104908 | 0.006354113 | 70699 | 71324 | 75104 | 0.949669778 | ||
| 1 | 1001020200 | 1.282175 | 1757 | 1370.327881 | 0.017074559 | 0.364826411 | 0.583949924 | 0.00284576 | 0 | 0.105864435 | ... | 0.684118688 | 0.224515289 | 0.208804905 | 0.006640106 | 50133 | 83750 | 47604 | 1.759305954 |
2 rows × 39 columns
socio['TRACT_FIPS20'] = socio['TRACT_FIPS20'].astype(str)
socio['TRACT_FIPS20'] = socio['TRACT_FIPS20'].str.zfill(11)
socio.head(2)
| TRACT_FIPS20 | ALAND20 | TOTPOP20 | POPDEN16_20 | PHISPANIC16_20 | PNHWHITE16_20 | PNHBLACK16_20 | PFBORN16_20 | PLIMENG16_20 | PED1_16_20 | ... | POWNOC16_20 | AFFLUENCE16_20 | DISADVANTAGE16_20 | HISPAN_FORBORN_LIMENG16_20 | MEDFAMINC16_20 | MEDFAMINC_NHWHITE16_20 | MEDFAMINC_BLACK16_20 | MEDFAMINC_HISPANIC16_20 | RATIO_MEDFAMINC_NHWTOB16_20 | RATIO_MEDFAMINC_NHWTOHISP16_20 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 01001020100 | 3.793571 | 1941 | 511.6551209 | 0.017001545 | 0.774858296 | 0.121071614 | 0.002060793 | 0 | 0.142857149 | ... | 0.731601715 | 0.256776839 | 0.171104908 | 0.006354113 | 70699 | 71324 | 75104 | 0.949669778 | ||
| 1 | 01001020200 | 1.282175 | 1757 | 1370.327881 | 0.017074559 | 0.364826411 | 0.583949924 | 0.00284576 | 0 | 0.105864435 | ... | 0.684118688 | 0.224515289 | 0.208804905 | 0.006640106 | 50133 | 83750 | 47604 | 1.759305954 |
2 rows × 39 columns
# Extracting portions
socio['State_Code'] = socio['TRACT_FIPS20'].str[:2]
socio['County_Code'] = socio['TRACT_FIPS20'].str[2:5]
socio['Census_Tract_Code'] = socio['TRACT_FIPS20'].str[-6:]
socio.head(2)
| TRACT_FIPS20 | ALAND20 | TOTPOP20 | POPDEN16_20 | PHISPANIC16_20 | PNHWHITE16_20 | PNHBLACK16_20 | PFBORN16_20 | PLIMENG16_20 | PED1_16_20 | ... | HISPAN_FORBORN_LIMENG16_20 | MEDFAMINC16_20 | MEDFAMINC_NHWHITE16_20 | MEDFAMINC_BLACK16_20 | MEDFAMINC_HISPANIC16_20 | RATIO_MEDFAMINC_NHWTOB16_20 | RATIO_MEDFAMINC_NHWTOHISP16_20 | State_Code | County_Code | Census_Tract_Code | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 01001020100 | 3.793571 | 1941 | 511.6551209 | 0.017001545 | 0.774858296 | 0.121071614 | 0.002060793 | 0 | 0.142857149 | ... | 0.006354113 | 70699 | 71324 | 75104 | 0.949669778 | 01 | 001 | 020100 | ||
| 1 | 01001020200 | 1.282175 | 1757 | 1370.327881 | 0.017074559 | 0.364826411 | 0.583949924 | 0.00284576 | 0 | 0.105864435 | ... | 0.006640106 | 50133 | 83750 | 47604 | 1.759305954 | 01 | 001 | 020200 |
2 rows × 42 columns
### Selecting New York State (State Code = 36)
socio_ny = socio[socio["State_Code"]=="36"]
### Selecting Kings County (county code = 047)
socio_kings = socio_ny[socio_ny['County_Code'] == '047']
socio_kings.head(2)
| TRACT_FIPS20 | ALAND20 | TOTPOP20 | POPDEN16_20 | PHISPANIC16_20 | PNHWHITE16_20 | PNHBLACK16_20 | PFBORN16_20 | PLIMENG16_20 | PED1_16_20 | ... | HISPAN_FORBORN_LIMENG16_20 | MEDFAMINC16_20 | MEDFAMINC_NHWHITE16_20 | MEDFAMINC_BLACK16_20 | MEDFAMINC_HISPANIC16_20 | RATIO_MEDFAMINC_NHWTOB16_20 | RATIO_MEDFAMINC_NHWTOHISP16_20 | State_Code | County_Code | Census_Tract_Code | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 50941 | 36047000100 | 0.080239 | 4539 | 56568.24609 | 0.069839172 | 0.645296335 | 0.146948665 | 0.124476761 | 0.022790698 | 0.003593145 | ... | 0.072368875 | 195292 | 228307 | 36 | 047 | 000100 | ||||
| 50942 | 36047000200 | 0.106923 | 992 | 9277.713867 | 0.73286289 | 0.164314523 | 0.021169355 | 0.353830636 | 0.273565561 | 0.37416777 | ... | 0.453419685 | 36 | 047 | 000200 |
2 rows × 42 columns
socio_kings = socio_kings.rename(columns={'Census_Tract_Code': 'TRACTCE'})
socio_kings.head(1)
| TRACT_FIPS20 | ALAND20 | TOTPOP20 | POPDEN16_20 | PHISPANIC16_20 | PNHWHITE16_20 | PNHBLACK16_20 | PFBORN16_20 | PLIMENG16_20 | PED1_16_20 | ... | HISPAN_FORBORN_LIMENG16_20 | MEDFAMINC16_20 | MEDFAMINC_NHWHITE16_20 | MEDFAMINC_BLACK16_20 | MEDFAMINC_HISPANIC16_20 | RATIO_MEDFAMINC_NHWTOB16_20 | RATIO_MEDFAMINC_NHWTOHISP16_20 | State_Code | County_Code | TRACTCE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 50941 | 36047000100 | 0.080239 | 4539 | 56568.24609 | 0.069839172 | 0.645296335 | 0.146948665 | 0.124476761 | 0.022790698 | 0.003593145 | ... | 0.072368875 | 195292 | 228307 | 36 | 047 | 000100 |
1 rows × 42 columns
socio_kings['TRACTCE'].nunique()
805
third_compiled = pd.merge(second_compiled, socio_kings, on='TRACTCE', how='left')
third_compiled.info()
<class 'geopandas.geodataframe.GeoDataFrame'> Int64Index: 754 entries, 0 to 753 Columns: 231 entries, STATEFP to County_Code_y dtypes: float64(135), geometry(1), int64(44), object(51) memory usage: 1.3+ MB
This database contains the following
2017 Eating & Drinking Data is used as a proxy to 2019 Food Access Research Atlas data
eating = pd.read_csv('eating.csv')
eating.head(2)
| tract_fips10 | year | population | aland10 | count_7225 | count_sales_7225 | count_emp_7225 | popden_7225 | popden_sales_7225 | popden_emp_7225 | ... | aden_emp_722515 | count_722410 | count_sales_722410 | count_emp_722410 | popden_722410 | popden_sales_722410 | popden_emp_722410 | aden_722410 | aden_sales_722410 | aden_emp_722410 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1001020100 | 2003 | 1858.883 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 1 | 1001020100 | 2004 | 1852.185 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
2 rows × 49 columns
eating['tract_fips10'] = eating['tract_fips10'].astype(str)
eating['tract_fips10'] = eating['tract_fips10'].str.zfill(11)
eating.head(2)
| tract_fips10 | year | population | aland10 | count_7225 | count_sales_7225 | count_emp_7225 | popden_7225 | popden_sales_7225 | popden_emp_7225 | ... | aden_emp_722515 | count_722410 | count_sales_722410 | count_emp_722410 | popden_722410 | popden_sales_722410 | popden_emp_722410 | aden_722410 | aden_sales_722410 | aden_emp_722410 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 01001020100 | 2003 | 1858.883 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 1 | 01001020100 | 2004 | 1852.185 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
2 rows × 49 columns
# Extracting portions
eating['State_Code'] = eating['tract_fips10'].str[:2]
eating['County_Code'] = eating['tract_fips10'].str[2:5]
eating['TRACTCE'] = eating['tract_fips10'].str[-6:]
### Selecting New York State (State Code = 36)
eating_ny = eating[eating["State_Code"]=="36"]
### Selecting Kings County (county code = 047)
eating_kings = eating_ny[eating_ny['County_Code'] == '047']
eating_kings_17 = eating_kings[eating_kings['year'] == 2017]
fourth_compiled = pd.merge(third_compiled, eating_kings_17, on='TRACTCE', how='left')
fourth_compiled.info()
<class 'geopandas.geodataframe.GeoDataFrame'> Int64Index: 754 entries, 0 to 753 Columns: 282 entries, STATEFP to County_Code dtypes: float64(167), geometry(1), int64(60), object(54) memory usage: 1.6+ MB
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fourth_compiled.geometry.__geo_interface__,
locations=fourth_compiled.index,
z=fourth_compiled['PovertyRate'],
colorscale='OrRd',
zmin=fourth_compiled['PovertyRate'].min(),
zmax=fourth_compiled['PovertyRate'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fourth_compiled.iterrows():
hover_text.append(
f"Census Tract Area: {row['TRACTCE']}<br>Population (2010): {row['Pop2010']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']}<br>Supermarkets: {row['count_445110']}<br>Speciality Stores: {row['count_4452']}<br>Warehouse Clubs/Supercentres: {row['count_452311']}<br>Fast Food Restaurants: {row['count_722513']}<br>Full Service Restaurants: {row['count_722511']}<br>Coffee Shops: {row['count_722515']}<br>Bars: {row['count_722410']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": fourth_compiled.geometry.centroid.y.mean(),
"lon": fourth_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='PovertyRate')
# Adding markers where LILATracts_halfAnd10 = 1
lila_tracts = fourth_compiled[fourth_compiled['LILATracts_halfAnd10'] == 1]
fig.add_trace(go.Scattermapbox(
lat=lila_tracts.geometry.centroid.y,
lon=lila_tracts.geometry.centroid.x,
mode='markers',
marker=dict(size=10, color='blue', opacity=0.7),
hoverinfo='skip'
))
fig.show()
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fourth_compiled.geometry.__geo_interface__,
locations=fourth_compiled.index,
z=fourth_compiled['count_722513'],
colorscale='blues',
zmin=fourth_compiled['count_722513'].min(),
zmax=fourth_compiled['count_722513'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fourth_compiled.iterrows():
hover_text.append(
f"Census Tract Area: {row['TRACTCE']}<br>Population (2010): {row['Pop2010']}<br>Fast Food Rests: {row['count_722513']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']} <br>UG & PG Edu: {row['PPROF16_20']}<br>TractSNAP (%): {round((row['TractSNAP']/row['OHU2010'])*100, 2)}<br>Supermarkets: {row['count_445110']}<br>Speciality Stores: {row['count_4452']}<br>Warehouse Clubs/Supercentres: {row['count_452311']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": fourth_compiled.geometry.centroid.y.mean(),
"lon": fourth_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='PovertyRate')
# Adding markers where LILATracts_halfAnd10 = 1
lila_tracts = fourth_compiled[fourth_compiled['LILATracts_halfAnd10'] == 1]
fig.add_trace(go.Scattermapbox(
lat=lila_tracts.geometry.centroid.y,
lon=lila_tracts.geometry.centroid.x,
mode='markers',
marker=dict(size=10, color='blue', opacity=0.7),
hoverinfo='skip'
))
fig.show()
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fourth_compiled.geometry.__geo_interface__,
locations=fourth_compiled.index,
z=fourth_compiled['PovertyRate'],
colorscale='OrRd',
zmin=fourth_compiled['PovertyRate'].min(),
zmax=fourth_compiled['PovertyRate'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fourth_compiled.iterrows():
hover_text.append(
f"Census Tract Area: {row['TRACTCE']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']}<br>White pop (%): {round((row['TractWhite']/row['Pop2010'])*100, 2)}<br>Black Pop (%): {round((row['TractBlack']/row['Pop2010'])*100, 2)}<br>Asian Pop (%): {round((row['TractAsian']/row['Pop2010'])*100, 2)}<br>Native Alaskan/Indian Pop (%): {round((row['TractAIAN']/row['Pop2010'])*100, 2)}<br>Hispanic Pop (%): {round((row['TractHispanic']/row['Pop2010'])*100, 2)}<br>Other/Mixed Pop (%): {round((row['TractOMultir']/row['Pop2010'])*100, 2)} <br>Employed with Professional Studies (Prop): {row['PPROF16_20']}<br>Households w public assist income/food stamps (%): {row['PPUBAS16_20']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": fourth_compiled.geometry.centroid.y.mean(),
"lon": fourth_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='PovertyRate')
fig.show()
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fourth_compiled.geometry.__geo_interface__,
locations=fourth_compiled.index,
z=fourth_compiled['PovertyRate'],
colorscale='OrRd',
zmin=fourth_compiled['PovertyRate'].min(),
zmax=fourth_compiled['PovertyRate'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fourth_compiled.iterrows():
hover_text.append(
f"Census Tract Area: {row['TRACTCE']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']}<br>White pop (%): {round((row['TractWhite']/row['Pop2010'])*100, 2)}<br>Black Pop (%): {round((row['TractBlack']/row['Pop2010'])*100, 2)}<br>Asian Pop (%): {round((row['TractAsian']/row['Pop2010'])*100, 2)}<br>Native Alaskan/Indian Pop (%): {round((row['TractAIAN']/row['Pop2010'])*100, 2)}<br>Hispanic Pop (%): {round((row['TractHispanic']/row['Pop2010'])*100, 2)}<br>Other/Mixed Pop (%): {round((row['TractOMultir']/row['Pop2010'])*100, 2)} <br>Female-headed families with kids (Prop): {row['PFHFAM16_20']}<br>Single-parent families with kids (Prop): {row['PSNGPNT16_20']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": fourth_compiled.geometry.centroid.y.mean(),
"lon": fourth_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='PovertyRate')
fig.show()
The socio-economic database has two important metrics to measure the level of socio-economic equity
The Affluence factor is the average of three variables given below
The Disadvantages factor is the average of three variables given below
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fourth_compiled.geometry.__geo_interface__,
locations=fourth_compiled.index,
z=fourth_compiled['PovertyRate'],
colorscale='OrRd',
zmin=fourth_compiled['PovertyRate'].min(),
zmax=fourth_compiled['PovertyRate'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fourth_compiled.iterrows():
hover_text.append(
f"Census Tract Area: {row['TRACTCE']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']}<br>White pop (%): {round((row['TractWhite']/row['Pop2010'])*100, 2)}<br>Black Pop (%): {round((row['TractBlack']/row['Pop2010'])*100, 2)}<br>Asian Pop (%): {round((row['TractAsian']/row['Pop2010'])*100, 2)}<br>Native Alaskan/Indian Pop (%): {round((row['TractAIAN']/row['Pop2010'])*100, 2)}<br>Hispanic Pop (%): {round((row['TractHispanic']/row['Pop2010'])*100, 2)}<br>Other/Mixed Pop (%): {round((row['TractOMultir']/row['Pop2010'])*100, 2)} <br>Disadvantage: {row['DISADVANTAGE16_20']}<br>Affluence: {row['AFFLUENCE16_20']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": fourth_compiled.geometry.centroid.y.mean(),
"lon": fourth_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='PovertyRate')
fig.show()